-- =============================================
-- This stored procedure inserts a new Nav_Menu record at a specified location.
--
-- Modifications
-- 07/10/2003    E.Tatsui
-- =============================================

CREATE                           PROCEDURE [dbo].[amsp_CMInsertNavMenu]
  @InTargetNavMenuID numeric,
  @InTitle varchar(255),
  @InName varchar(255),
  @InContactID numeric,
  @InWebsiteKey uniqueidentifier,
  @InContentAuthorityGroupID numeric,
  @OutNavMenuID numeric OUTPUT
AS
BEGIN

  DECLARE
  @LowestSortOrder numeric(28,18),
  @TargetAncestorNavMenuID numeric,
  @TargetExpirationDays int,
  @TargetCategoryDepth int,
  @TargetTemplatePath varchar(255),
  @TargetNavContentGroupInd char(1),
  @TargetContentAuthorityGroupID numeric,
  @TargetMicrositeFlag char(1),
  @TargetWebsiteKey uniqueidentifier,
  @ParentNavMenuID numeric,
  @AncestorNavMenuID numeric,
  @ExpirationDays int,
  @MaxSortOrder numeric(28,18),
  @MinSortOrder numeric(28,18),
  @LastSortOrder numeric(28,18),
  @BelowSortOrder numeric(28,18),
  @WebsiteKey uniqueidentifier,
  @CMExpDaysDefault int,
  @NavMenuID numeric
  
  EXEC amsp_CMNavMenuRenum
  
  -- Creating a new website. Set default properties.
  IF @InTargetNavMenuID IS NULL BEGIN
    SELECT @LowestSortOrder = Max(SortOrder)
      FROM Nav_Menu
     WHERE NavContentGroupInd = 'N'
    SET @TargetCategoryDepth = -1
    SET @TargetNavContentGroupInd = 'N'
    SET @TargetContentAuthorityGroupID = @InContentAuthorityGroupID
  END
  ELSE
    -- Get info on target item.
    SELECT @LowestSortOrder = SortOrder,
           @TargetAncestorNavMenuID = AncestorNavMenuID,
           @TargetExpirationDays = ExpirationDays,
           @TargetCategoryDepth = CategoryDepth,
           @TargetTemplatePath = TemplatePath,
           @TargetNavContentGroupInd = NavContentGroupInd,
           @TargetContentAuthorityGroupID = ContentAuthorityGroupID,
           @TargetMicrositeFlag = MicrositeFlag,
           @TargetWebsiteKey = WebsiteKey
      FROM Nav_Menu
     WHERE NavMenuID = @InTargetNavMenuID
  
  IF @InContentAuthorityGroupID IS NOT NULL AND @TargetContentAuthorityGroupID <> @InContentAuthorityGroupID
    SET @TargetContentAuthorityGroupID = @InContentAuthorityGroupID

  SELECT @CMExpDaysDefault = CAST(Value as numeric)
    FROM System_Variable
   WHERE Name = 'CMExpDaysDefault'
  
  -- Is target Web site name? If so, must create new main menu item.
  IF @TargetAncestorNavMenuID IS NULL BEGIN
    SET @ParentNavMenuID = NULL
    SET @AncestorNavMenuID = NULL
    SET @ExpirationDays = @CMExpDaysDefault
  END
  ELSE BEGIN -- Target NOT Web site name or content folder.
    SET @ParentNavMenuID = @InTargetNavMenuID
    SET @AncestorNavMenuID = @TargetAncestorNavMenuID
    SET @ExpirationDays = @TargetExpirationDays
  END

  -- Get the min and max sort order of the target family.
  SELECT @MinSortOrder = a.SortOrder,
         @MaxSortOrder = (SELECT Min(x.SortOrder)
                    FROM Nav_Menu x
                    WITH (NOLOCK)
                   WHERE x.SortOrder > a.SortOrder
                     AND x.CategoryDepth <= a.CategoryDepth)
    FROM Nav_Menu a WITH (NOLOCK)
   WHERE a.NavMenuID = @InTargetNavMenuID

  -- Find out the last sort order from target descendants.
  IF @MaxSortOrder IS NOT NULL
    SELECT @LastSortOrder = Max(a.SortOrder)
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE a.SortOrder > @MinSortOrder
       AND a.SortOrder < @MaxSortOrder
       AND a.NavContentGroupInd = @TargetNavContentGroupInd
  ELSE
    SELECT @LastSortOrder = Max(a.SortOrder)
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE a.SortOrder > @MinSortOrder
       AND a.NavContentGroupInd = @TargetNavContentGroupInd

  IF @LastSortOrder IS NOT NULL
    SET @LowestSortOrder = @LastSortOrder

  -- Find the item right below the target item.
  SELECT @BelowSortOrder = Min(SortOrder)
    FROM Nav_Menu WITH (NOLOCK)
   WHERE SortOrder > @LowestSortOrder
  
  IF @BelowSortOrder IS NULL
    SET @BelowSortOrder = @LowestSortOrder + 1

  IF @TargetWebsiteKey IS NOT NULL
    SET @WebsiteKey = @TargetWebsiteKey
  ELSE IF @TargetNavContentGroupInd = 'C'
    SELECT TOP 1 @WebsiteKey = WebsiteKey
      FROM Website
     WHERE SimpleAdvancedInd = 'A'
       AND ActiveFlag = 'Y'
       AND IsiMISWebsite = 0
       AND Cast(WebsiteKey as varchar(100)) <> (SELECT Value FROM System_Variable WHERE Name = 'CMAdminWebsiteKey')
  ELSE
    SET @WebsiteKey = @InWebsiteKey

  INSERT INTO Nav_Menu (
         Name,
         Title,
         ParentNavMenuID,
         AncestorNavMenuID,
         CategoryDepth,
         SortOrder,
         TemplatePath,
         WorkflowStatusCode,
         NavContentGroupInd,
         ContentAuthorityGroupID,
         DirectListComboInd,
         OwnerContactID,
         LastUpdatedByContactID,
         ExpirationDays,
         MicrositeFlag,
         WebsiteKey)
  VALUES (
         @InName,
         @InTitle,
         @ParentNavMenuID,
         @AncestorNavMenuID,
         @TargetCategoryDepth + 1,
         (@LowestSortOrder + @BelowSortOrder)/2,
         @TargetTemplatePath,
         'W',
         @TargetNavContentGroupInd,
         @TargetContentAuthorityGroupID,
         'D',
         @InContactID,
         @InContactID,
         @ExpirationDays,
         @TargetMicrositeFlag,
         @WebsiteKey)
  
  SELECT @NavMenuID = @@Identity
    
  -- If ancestor is not set previously, set it to itself now.
  IF @AncestorNavMenuID IS NULL AND @InTargetNavMenuID IS NOT NULL
    UPDATE Nav_Menu
       SET AncestorNavMenuID = @NavMenuID
     WHERE NavMenuID = @NavMenuID

  INSERT INTO Nav_Menu_Workflow_Log (
         NavMenuID,
         WorkflowStatusCode,
         ContactID,
         ChangeDateTime)
  VALUES (
         @NavMenuID,
         'W',
         @InContactID,
         CURRENT_TIMESTAMP)

  -- For content folder, we need to move it to right place.
  IF @TargetNavContentGroupInd = 'C'
    EXEC amsp_CMMoveContentFolder @NavMenuID, @InTargetNavMenuID, NULL

  -- Figure out FilePath, DescendantCount and AncestoryList
  EXEC amsp_CMUpdateNavProperties @NavMenuID

  SET @OutNavMenuID = @NavMenuID
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMInsertNavMenu] TO [IMIS]
GO